from typing import Optional, Dict, Any, List
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, text, inspect
from fastapi import HTTPException, status

from ..schemas.table_schemas import (
    TableBasicInfo, TableStructureInfo, TableColumnInfo, 
    TableIndexInfo, TableForeignKeyInfo, TableListResponse,
    DatabaseSummaryInfo, TableSearchRequest, TableColumnSearchRequest
)
import math


class TableService:
    """表结构服务类"""
    
    @staticmethod
    async def get_all_tables(db: AsyncSession) -> List[Dict[str, str]]:
        """获取所有表名和表注释"""
        # 使用information_schema查询获取表名和注释
        query = text("""
            SELECT 
                TABLE_NAME,
                COALESCE(TABLE_COMMENT, '') as TABLE_COMMENT
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_TYPE = 'BASE TABLE'
            ORDER BY TABLE_NAME
        """)
        result = await db.execute(query)
        tables = []
        for row in result.fetchall():
            table_name = row[0]
            table_comment = row[1] or ''
            # 如果有注释，格式为 "表名 (注释)"，否则只显示表名
            display_name = f"{table_name} ({table_comment})" if table_comment else table_name
            tables.append({
                "value": table_name,
                "label": display_name,
                "comment": table_comment
            })
        return tables
    
    @staticmethod
    async def get_table_structure(db: AsyncSession, table_name: str) -> TableStructureInfo:
        """获取指定表的详细结构信息"""
        # 检查表是否存在
        all_tables = await TableService.get_all_tables(db)
        table_names = [table["value"] for table in all_tables]
        if table_name not in table_names:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Table '{table_name}' not found"
            )
        
        # 获取列信息
        columns_query = text(f"""
            SELECT 
                COLUMN_NAME,
                DATA_TYPE,
                IS_NULLABLE,
                COLUMN_DEFAULT,
                COLUMN_COMMENT,
                EXTRA,
                CHARACTER_MAXIMUM_LENGTH,
                NUMERIC_PRECISION,
                NUMERIC_SCALE,
                COLUMN_KEY
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name
            ORDER BY ORDINAL_POSITION
        """)
        
        columns_result = await db.execute(columns_query, {"table_name": table_name})
        columns_data = columns_result.fetchall()
        
        columns = []
        primary_keys = []
        
        for col_data in columns_data:
            column_name = col_data[0]
            data_type = col_data[1]
            is_nullable = col_data[2] == 'YES'
            column_default = col_data[3]
            column_comment = col_data[4] or ''
            extra = col_data[5] or ''
            char_max_length = col_data[6]
            numeric_precision = col_data[7]
            numeric_scale = col_data[8]
            column_key = col_data[9]
            
            is_primary_key = column_key == 'PRI'
            is_auto_increment = 'auto_increment' in extra.lower()
            
            if is_primary_key:
                primary_keys.append(column_name)
            
            column = TableColumnInfo(
                column_name=column_name,
                data_type=data_type,
                is_nullable=is_nullable,
                column_default=str(column_default) if column_default is not None else None,
                column_comment=column_comment,
                is_primary_key=is_primary_key,
                is_auto_increment=is_auto_increment,
                character_maximum_length=char_max_length,
                numeric_precision=numeric_precision,
                numeric_scale=numeric_scale
            )
            columns.append(column)
        
        # 获取索引信息
        indexes_query = text(f"""
            SELECT 
                INDEX_NAME,
                COLUMN_NAME,
                NON_UNIQUE
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name
            ORDER BY INDEX_NAME, SEQ_IN_INDEX
        """)
        
        indexes_result = await db.execute(indexes_query, {"table_name": table_name})
        indexes_data = indexes_result.fetchall()
        
        # 组织索引信息
        indexes_dict = {}
        for idx_data in indexes_data:
            index_name = idx_data[0]
            column_name = idx_data[1]
            non_unique = idx_data[2]
            
            if index_name not in indexes_dict:
                indexes_dict[index_name] = {
                    'columns': [],
                    'is_unique': non_unique == 0
                }
            indexes_dict[index_name]['columns'].append(column_name)
        
        indexes = []
        for index_name, index_info in indexes_dict.items():
            if index_name != 'PRIMARY':  # 排除主键索引
                index = TableIndexInfo(
                    index_name=index_name,
                    column_names=index_info['columns'],
                    is_unique=index_info['is_unique']
                )
                indexes.append(index)
        
        # 获取外键信息
        foreign_keys_query = text(f"""
            SELECT 
                CONSTRAINT_NAME,
                COLUMN_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME,
                UPDATE_RULE,
                DELETE_RULE
            FROM information_schema.KEY_COLUMN_USAGE kcu
            JOIN information_schema.REFERENTIAL_CONSTRAINTS rc 
                ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
                AND kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
            WHERE kcu.TABLE_SCHEMA = DATABASE() 
                AND kcu.TABLE_NAME = :table_name
                AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
        """)
        
        fk_result = await db.execute(foreign_keys_query, {"table_name": table_name})
        fk_data = fk_result.fetchall()
        
        # 组织外键信息
        fk_dict = {}
        for fk_row in fk_data:
            constraint_name = fk_row[0]
            column_name = fk_row[1]
            referenced_table = fk_row[2]
            referenced_column = fk_row[3]
            update_rule = fk_row[4]
            delete_rule = fk_row[5]
            
            if constraint_name not in fk_dict:
                fk_dict[constraint_name] = {
                    'columns': [],
                    'referenced_table': referenced_table,
                    'referenced_columns': [],
                    'update_rule': update_rule,
                    'delete_rule': delete_rule
                }
            fk_dict[constraint_name]['columns'].append(column_name)
            fk_dict[constraint_name]['referenced_columns'].append(referenced_column)
        
        foreign_keys = []
        for constraint_name, fk_info in fk_dict.items():
            foreign_key = TableForeignKeyInfo(
                constraint_name=constraint_name,
                column_names=fk_info['columns'],
                referenced_table=fk_info['referenced_table'],
                referenced_columns=fk_info['referenced_columns'],
                on_update=fk_info['update_rule'],
                on_delete=fk_info['delete_rule']
            )
            foreign_keys.append(foreign_key)
        
        # 获取表注释
        table_comment_query = text(f"""
            SELECT table_comment 
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() AND table_name = :table_name
        """)
        
        comment_result = await db.execute(table_comment_query, {"table_name": table_name})
        comment_row = comment_result.fetchone()
        table_comment = comment_row[0] if comment_row and comment_row[0] else None
        
        return TableStructureInfo(
            table_name=table_name,
            table_comment=table_comment,
            columns=columns,
            primary_keys=primary_keys,
            indexes=indexes,
            foreign_keys=foreign_keys,
            column_count=len(columns),
            index_count=len(indexes),
            foreign_key_count=len(foreign_keys)
        )
    
    @staticmethod
    async def get_tables_with_pagination(db: AsyncSession, query_params: TableSearchRequest) -> Dict[str, Any]:
        """分页获取表信息"""
        # 构建基础查询
        base_query = text("""
            SELECT 
                t.TABLE_NAME,
                t.TABLE_COMMENT,
                (SELECT COUNT(*) FROM information_schema.COLUMNS c 
                 WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME) as column_count,
                (SELECT COUNT(DISTINCT s.INDEX_NAME) FROM information_schema.STATISTICS s 
                 WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME) as index_count,
                (SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE k 
                 WHERE k.TABLE_SCHEMA = t.TABLE_SCHEMA AND k.TABLE_NAME = t.TABLE_NAME 
                 AND k.REFERENCED_TABLE_NAME IS NOT NULL) as foreign_key_count
            FROM information_schema.TABLES t
            WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE'
        """)
        
        conditions = []
        params = {}
        
        # 添加搜索条件
        if query_params.keyword:
            conditions.append("t.TABLE_NAME LIKE :keyword")
            params["keyword"] = f"%{query_params.keyword}%"
        
        if query_params.table_type:
            if query_params.table_type == 'system':
                conditions.append("t.TABLE_NAME LIKE 'sys_%'")
            elif query_params.table_type == 'business':
                conditions.append("t.TABLE_NAME NOT LIKE 'sys_%'")
        
        # 构建完整查询
        where_clause = ""
        if conditions:
            where_clause = " AND " + " AND ".join(conditions)
        
        # 计算总数
        count_query = text(f"""
            SELECT COUNT(*) 
            FROM information_schema.TABLES t
            WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE'{where_clause}
        """)
        
        count_result = await db.execute(count_query, params)
        total = count_result.scalar() or 0
        
        # 分页查询
        page = query_params.page
        size = query_params.size
        offset = (page - 1) * size
        
        paginated_query = text(f"""
            {base_query.text}{where_clause}
            ORDER BY t.TABLE_NAME
            LIMIT :limit OFFSET :offset
        """)
        
        params.update({"limit": size, "offset": offset})
        
        result = await db.execute(paginated_query, params)
        rows = result.fetchall()
        
        items = []
        for row in rows:
            table_info = TableBasicInfo(
                table_name=row[0],
                table_comment=row[1] if row[1] else None,
                column_count=row[2] or 0,
                index_count=row[3] or 0,
                foreign_key_count=row[4] or 0
            )
            items.append(table_info)
        
        pages = math.ceil(total / size) if size > 0 else 0
        
        return {
            "total": total,
            "items": items,
            "page": page,
            "size": size,
            "pages": pages
        }
    
    @staticmethod
    async def get_table_columns(db: AsyncSession, request: TableColumnSearchRequest) -> List[TableColumnInfo]:
        """获取表的列信息"""
        # 检查表是否存在
        all_tables = await TableService.get_all_tables(db)
        table_names = [table["value"] for table in all_tables]
        if request.table_name not in table_names:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Table '{request.table_name}' not found"
            )
        
        # 构建查询条件
        where_conditions = ["TABLE_SCHEMA = DATABASE()", "TABLE_NAME = :table_name"]
        params = {"table_name": request.table_name}
        
        if request.keyword:
            where_conditions.append("COLUMN_NAME LIKE :keyword")
            params["keyword"] = f"%{request.keyword}%"
        
        where_clause = " AND ".join(where_conditions)
        
        columns_query = text(f"""
            SELECT 
                COLUMN_NAME,
                DATA_TYPE,
                IS_NULLABLE,
                COLUMN_DEFAULT,
                COLUMN_COMMENT,
                EXTRA,
                CHARACTER_MAXIMUM_LENGTH,
                NUMERIC_PRECISION,
                NUMERIC_SCALE,
                COLUMN_KEY
            FROM information_schema.COLUMNS 
            WHERE {where_clause}
            ORDER BY ORDINAL_POSITION
        """)
        
        result = await db.execute(columns_query, params)
        rows = result.fetchall()
        
        columns = []
        for row in rows:
            column = TableColumnInfo(
                column_name=row[0],
                data_type=row[1],
                is_nullable=row[2] == 'YES',
                column_default=str(row[3]) if row[3] is not None else None,
                column_comment=row[4] or '',
                is_primary_key=row[9] == 'PRI',
                is_auto_increment='auto_increment' in (row[5] or '').lower(),
                character_maximum_length=row[6],
                numeric_precision=row[7],
                numeric_scale=row[8]
            )
            columns.append(column)
        
        return columns
    
    @staticmethod
    async def search_tables(db: AsyncSession, keyword: str) -> List[Dict[str, str]]:
        """根据关键词搜索表名"""
        if not keyword:
            return await TableService.get_all_tables(db)
        
        query = text("""
            SELECT 
                TABLE_NAME,
                COALESCE(TABLE_COMMENT, '') as TABLE_COMMENT
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_TYPE = 'BASE TABLE'
                AND (TABLE_NAME LIKE :keyword OR TABLE_COMMENT LIKE :keyword)
            ORDER BY TABLE_NAME
        """)
        
        result = await db.execute(query, {"keyword": f"%{keyword}%"})
        tables = []
        for row in result.fetchall():
            table_name = row[0]
            table_comment = row[1] or ''
            # 如果有注释，格式为 "表名 (注释)"，否则只显示表名
            display_name = f"{table_name} ({table_comment})" if table_comment else table_name
            tables.append({
                "value": table_name,
                "label": display_name,
                "comment": table_comment
            })
        return tables
    
    @staticmethod
    async def get_database_summary(db: AsyncSession) -> DatabaseSummaryInfo:
        """获取数据库概要信息"""
        # 获取表统计信息
        summary_query = text("""
            SELECT 
                COUNT(*) as total_tables,
                SUM(CASE WHEN TABLE_NAME LIKE 'sys_%' THEN 1 ELSE 0 END) as system_tables,
                SUM(CASE WHEN TABLE_NAME NOT LIKE 'sys_%' THEN 1 ELSE 0 END) as business_tables
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'
        """)
        
        summary_result = await db.execute(summary_query)
        summary_row = summary_result.fetchone()
        
        total_tables = summary_row[0] or 0
        system_tables_count = summary_row[1] or 0
        business_tables_count = summary_row[2] or 0
        
        # 获取列总数
        columns_query = text("""
            SELECT COUNT(*) 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE()
        """)
        
        columns_result = await db.execute(columns_query)
        total_columns = columns_result.scalar() or 0
        
        # 获取索引总数
        indexes_query = text("""
            SELECT COUNT(DISTINCT CONCAT(TABLE_NAME, '.', INDEX_NAME))
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE()
        """)
        
        indexes_result = await db.execute(indexes_query)
        total_indexes = indexes_result.scalar() or 0
        
        # 获取外键总数
        fk_query = text("""
            SELECT COUNT(DISTINCT CONSTRAINT_NAME)
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = DATABASE() 
                AND REFERENCED_TABLE_NAME IS NOT NULL
        """)
        
        fk_result = await db.execute(fk_query)
        total_foreign_keys = fk_result.scalar() or 0
        
        # 按前缀分组表
        prefix_query = text("""
            SELECT 
                CASE 
                    WHEN TABLE_NAME LIKE '%_%' THEN SUBSTRING_INDEX(TABLE_NAME, '_', 1)
                    ELSE 'other'
                END as prefix,
                COUNT(*) as count
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'
            GROUP BY prefix
            ORDER BY count DESC
        """)
        
        prefix_result = await db.execute(prefix_query)
        prefix_rows = prefix_result.fetchall()
        
        tables_by_type = {}
        for row in prefix_rows:
            tables_by_type[row[0]] = row[1]
        
        return DatabaseSummaryInfo(
            total_tables=total_tables,
            total_columns=total_columns,
            total_indexes=total_indexes,
            total_foreign_keys=total_foreign_keys,
            system_tables_count=system_tables_count,
            business_tables_count=business_tables_count,
            tables_by_type=tables_by_type
        )


# 创建服务实例
table_service = TableService()